IF OBJECT_ID('ssf.job_wait_until_complete') IS NOT NULL
	DROP PROCEDURE ssf.job_wait_until_complete
GO

CREATE PROCEDURE ssf.job_wait_until_complete
	@job_name NVARCHAR(128)
	, @run_after_datetime DATETIME = NULL
	, @poll_interval TIME(1) = NULL
	, @run_status INT = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@job_id UNIQUEIDENTIFIER = NULL
			, @current_datetime DATETIME = GETDATE()
			, @job_run_request_datetime DATETIME = NULL
			, @job_run_datetime DATETIME = NULL
			, @poll_interval_string VARCHAR(12)
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameters]]></code_comment>*/
SET @run_status = NULL
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@job_name, N'') IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[set variables]]></code_comment>*/
IF @poll_interval IS NULL
OR @poll_interval < '00:00:00.1'
	SET @poll_interval = '00:00:10.0'
SET @poll_interval_string = CAST(@poll_interval AS VARCHAR(12))

SET @run_after_datetime = ssf.job_datetime_floor(@run_after_datetime)
-- ============

-- ============
/*<code_comment><![CDATA[get job_id]]></code_comment>*/
SET @job_id = ssf.job_get_job_id(@job_name)
-- ============

-- ============
/*<code_comment><![CDATA[fail if job does not exist]]></code_comment>*/
IF @job_id IS NULL
BEGIN
	RAISERROR('The job <%s> does not exist.', 16, 1, @job_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[if no run date was given, determine whether to set a run date]]></code_comment>*/
IF @run_after_datetime IS NULL
BEGIN
	-- get the latest run date for the job, at or before execution time
	SET @job_run_datetime = ssf.job_get_last_run_datetime(@job_id, @current_datetime)
	
	-- get the latest run request date for the job, at or before execution time
	SET @job_run_request_datetime = ssf.job_get_last_run_request_datetime(@job_id, @current_datetime)
	
	-- if the job's latest requested run datetime is after its last run datetime and before execution time,
	-- it is still running - use requested run date as run-after-date
	IF @job_run_request_datetime > @job_run_datetime
		SET @run_after_datetime = @job_run_request_datetime
END
-- ============

-- ============
/*<code_comment><![CDATA[if run date still not set, set last status and exit]]></code_comment>*/
IF @run_after_datetime IS NULL
BEGIN
	SET @run_status = ssf.job_get_run_status(@job_id, @job_run_datetime)
	
	RETURN @@ERROR
END
-- ============

-- ============
/*<code_comment><![CDATA[begin loop: wait until job has completed, having started at or after the given run date]]></code_comment>*/
WHILE 1 = 1
BEGIN
	-- ============
	/*<code_comment><![CDATA[get job run datetime]]></code_comment>*/
	SET @job_run_datetime = ssf.job_get_first_run_datetime(@job_id, @run_after_datetime)
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[exit loop if job completed]]></code_comment>*/
	IF @job_run_datetime IS NOT NULL
		BREAK
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[wait for poll interval]]></code_comment>*/
	WAITFOR DELAY @poll_interval_string
	-- ============
END
/*<code_comment><![CDATA[end loop: wait until job has completed, having started at or after the given run date]]></code_comment>*/
-- ============

-- ============
/*<code_comment><![CDATA[get run status]]></code_comment>*/
SET @run_status = ssf.job_get_run_status(@job_id, @job_run_datetime)
-- ============

RETURN @@ERROR
GO
